﻿CREATE VIEW dbo.v_CreditInfo_20
AS
SELECT     Borrower.LoanID, CI.DatePulled, dbo.f_InstallmentsTotal(Borrower.LoanID) AS Installment, dbo.f_RevolvingsTotal(Borrower.LoanID) AS Revolving, 
                      dbo.f_MortgageStatus(Borrower.LoanID) AS MortgageStatus, CONVERT(int, dbo.f_PriorRepos(Borrower.LoanID, CI.Pulled, CI.DatePulled)) 
                      AS PriorRepos, dbo.f_ReposCount(Borrower.LoanID) AS ReposCount, dbo.f_ExcelAutoCredit(Borrower.LoanID, CI.Pulled) AS ExcelAutoCredit, 
                      dbo.f_PosAutoCredit(Borrower.LoanID, CI.Pulled, CI.DatePulled) AS PosAutoCredit, NULLIF (dbo.f_PosAutoAfterRepo(Borrower.LoanID, CI.Pulled), 0) 
                      AS PosAutoAfterRepo, dbo.f_AutoCreditCount(Borrower.LoanID) AS AutoCreditCount, dbo.f_AutoCreditCount_ALL(Borrower.LoanID) 
                      AS AutoCreditCount_ALL, dbo.f_OpenUnitsAdjusted(Borrower.LoanID) AS OpenUnitsAdjusted, dbo.f_PriorPayCar(Borrower.LoanID) AS PriorPayCar, 
                      dbo.f_HighCredit(Borrower.LoanID, CI.Pulled, CI.DatePulled) AS HighCredit, dbo.f_PayHistory(Borrower.LoanID, CI.Pulled, CI.DatePulled) AS PayHistory, 
                      dbo.f_DerogCountAfterDate(Borrower.LoanID, CI.BK_7_Date) AS DerogCountAfterBK, dbo.f_CurrDerogCredit(Borrower.LoanID) AS CurrDerogCredit, 
                      dbo.f_PosCreditTime(Borrower.LoanID, '', CI.Pulled) AS PosCreditTime, dbo.MaxPaymentHist(Borrower.LoanID, CI.DatePulled) AS MaxPaymentHist, 
                      dbo.f_CurrDerogAuto(Borrower.LoanID) AS CurrDerogAuto, dbo.f_GoodToAllAcctRatio(Borrower.LoanID, CI.Pulled) AS GoodToAllAcctRatio, 
                      dbo.f_PosAutoAfterBK(Borrower.LoanID, CI.BankruptcyDate) AS PosAutoAfterBK, dbo.f_LastLate(Borrower.LoanID) AS LastLate, 
                      dbo.f_LastLate_Auto(Borrower.LoanID) AS LastLate_Auto, dbo.f_PastDueRatio_All_W(Borrower.LoanID) AS PastDueRatio, 
                      dbo.f_BalanceToHighCredit(Borrower.LoanID) AS BalanceToHighCredit, dbo.f_AverageStatus(Borrower.LoanID) AS AverageStatus, 
                      dbo.f_Ghost(Borrower.LoanID, CI.Pulled) AS Ghost, dbo.f_LastLastDate(Borrower.LoanID, CI.Pulled) AS LastLastDate, 
                      dbo.f_PosCreditCount(Borrower.LoanID, '', CI.Pulled) AS PosCreditCount, dbo.f_CurrCreditCount(Borrower.LoanID, '', CI.Pulled) AS CurrCreditCount, 
                      CI.ReportXML, CI.ReportMerged, CI.ScoreEfx, CI.ScoreXpn, CI.ScoreTU, CI.ScoreTri, CI.Chapter7_FileDate, CI.Chapter7_SatisfiedDate, 
                      CI.Chapter7_Status, dbo.MaxNum(CONVERT(bit, CI.Chapter7_Status), CI.Chapter7_Count) AS Chapter7_Count, CI.Chapter13_FileDate, 
                      CI.Chapter13_SatisfiedDate, CI.Chapter13_Status, dbo.MaxNum(CONVERT(bit, CI.Chapter13_Status), CI.Chapter13_Count) AS Chapter13_Count, 
                      CI.ForeclosureNOD_FileDate, CI.ForeclosureNOD_SatisfiedDate, CI.ForeclosureNOD_Status, dbo.MaxNum(CONVERT(bit, CI.ForeclosureNOD_Status), 
                      CI.ForeclosureNOD_Count) AS ForeclosureNOD_Count, CI.BK_FC_NOD_Count, CI.DebtRatio, CI.CashReserveMos, CI.MortgageCount, 
                      CI.MortgageMonthReviewedMax, CI.MortgageSince, CI.MortgageHistory_30, CI.MortgageHistory_60, CI.MortgageHistory_90, CI.MortgageHistory_120, 
                      CI.TradeLinesTotal, CI.TradeLinesCount_30, CI.TradeLinesCount_60, CI.TradeLinesCount_90, CI.TradeLinesCount_120, CI.ChargeOffsCount, 
                      CI.ChargeOffsAmount, CI.TradeLinesCount_ActiveOpen_03, CI.TradeLinesCount_ActiveOpen_06, CI.TradeLinesCount_ActiveOpen_12, 
                      CI.TradeLinesCount_ActiveOpen_24, CI.TradeLinesCount_ActiveOpen_12_1000, CI.TradeLinesCount_ActiveOpen_12_5000, 
                      CI.TradeLinesCount_MR_24_5000, CI.PaymentDelayAverage, CI.MortgageLate, CI.RepoCount, CI.OpenBK, CI.PhoneUtilityCollection, CI.SSN_DL, 
                      CI.AddressMatches, CI.AnyWho, CI.DLSuspended, L_HP.HousePayment, ISNULL(L_GI.GrossIncome, 0) + ISNULL(L_NonEI.NonEmploymentIncome, 0) 
                      AS GrossIncome, dbo.f_RepoAfterBK(Borrower.LoanID, CI.Pulled, CI.BankruptcyDate) AS RepoAfterBK
FROM         dbo.t_CreditInfo AS CI INNER JOIN
                      dbo.t_Loan_Borrower AS Borrower ON CI.BorrowerID = Borrower.BorrowerID AND Borrower.Pos = 0 INNER JOIN
                      dbo.v_Loan_GrossIncome AS L_GI ON Borrower.LoanID = L_GI.LoanID INNER JOIN
                      dbo.v_Loan_HousPayment AS L_HP ON Borrower.LoanID = L_HP.LoanID INNER JOIN
                      dbo.t_Borrower ON CI.BorrowerID = dbo.t_Borrower.ID INNER JOIN
                      dbo.v_Loan_NonEmploymentIncome AS L_NonEI ON Borrower.LoanID = L_NonEI.LoanID

GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPane1', @value = N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[31] 4[30] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "CI"
            Begin Extent = 
               Top = 1
               Left = 114
               Bottom = 110
               Right = 385
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "Borrower"
            Begin Extent = 
               Top = 11
               Left = 603
               Bottom = 105
               Right = 754
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "L_GI"
            Begin Extent = 
               Top = 243
               Left = 360
               Bottom = 337
               Right = 511
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "L_HP"
            Begin Extent = 
               Top = 287
               Left = 121
               Bottom = 366
               Right = 273
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "t_Borrower"
            Begin Extent = 
               Top = 171
               Left = 95
               Bottom = 280
               Right = 317
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "L_NonEI"
            Begin Extent = 
               Top = 362
               Left = 527
               Bottom = 455
               Right = 719
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 7260
         Alias = 17', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'VIEW', @level1name = N'v_CreditInfo_20';


GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPane2', @value = N'55
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'VIEW', @level1name = N'v_CreditInfo_20';


GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPaneCount', @value = 2, @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'VIEW', @level1name = N'v_CreditInfo_20';

